﻿





-- 查询人员信息
CREATE procedure [dbo].[RyInfo_Get]
@sfzh varchar(18) = null,
@xm varchar(10) = null,
@sfxyg bit = null,
@ygzt bit = null,
@bzid varchar(100)=null,
@rygz varchar(100) = null,
@RowCount int = 200
as
set rowcount @RowCount
select Ry_Info.sfzh,xm,xb,case xb when 1 then '男' else '女' end xb_desc,jg,gh,csrq,mz,ywpx,xl,zz,lxdh,jjllr,lldh,xp,gz1,gz2,gz3,gz4,gz5,bzmc qybmm,sfxyg,
case sfxyg when 1 then '是' else '否' end sfxyg_desc,ygzt,case ygzt when 1 then '用工' else '退工' end ygzt_desc,sgz,jndj1,jndj2,jndj3,fbmc zbfbmc,Banz_Info.bzid,Fenb_Info.fbid,
htbh,xx,aqjycj,htyjpath,intime,outtime
from Ry_Info 
left join Bzry_Info on(Ry_Info.sfzh=Bzry_Info.sfzh)
left join Banz_Info on(Bzry_Info.bzid=Banz_Info.bzid)
left join Fenbry_Info on(Ry_Info.sfzh=Fenbry_Info.sfzh)
left join Fenb_Info on(Fenbry_Info.fbid=Fenb_Info.fbid)
where
(Ry_Info.sfzh like @sfzh+'%' or @sfzh is null) 
and (xm like '%'+@xm+'%' or @xm is null) 
and (sfxyg=@sfxyg or @sfxyg is null) 
and (ygzt=@ygzt or @ygzt is null)
and (Banz_Info.bzid=@bzid or @bzid is null)
and(@rygz is null or gz1=@rygz or gz2=@rygz or gz3=@rygz or gz4=@rygz or gz5=@rygz or @rygz=isnull(gz1,'未输入'))
order by createtime desc

select ygrs+tgrs zrs,ygrs,tgrs from
(
select count(1) ygrs from Ry_Info 
left join Bzry_Info on(Ry_Info.sfzh=Bzry_Info.sfzh)
left join Banz_Info on(Bzry_Info.bzid=Banz_Info.bzid)
where 
(Ry_Info.sfzh like @sfzh+'%' or @sfzh is null) 
and (xm like '%'+@xm+'%' or @xm is null) 
and (sfxyg=@sfxyg or @sfxyg is null) 
and (ygzt=@ygzt or @ygzt is null) and ygzt=1
and (Banz_Info.bzid=@bzid or @bzid is null)
and(@rygz is null or gz1=@rygz or gz2=@rygz or gz3=@rygz or gz4=@rygz or gz5=@rygz or @rygz=isnull(gz1,'未输入'))
) aa
inner join
(
select count(1) tgrs from Ry_Info 
left join Bzry_Info on(Ry_Info.sfzh=Bzry_Info.sfzh)
left join Banz_Info on(Bzry_Info.bzid=Banz_Info.bzid)
where 
(Ry_Info.sfzh like @sfzh+'%' or @sfzh is null) 
and (xm like '%'+@xm+'%' or @xm is null) 
and (sfxyg=@sfxyg or @sfxyg is null) 
and (ygzt=@ygzt or @ygzt is null) and ygzt=0
and (Banz_Info.bzid=@bzid or @bzid is null)
and(@rygz is null or gz1=@rygz or gz2=@rygz or gz3=@rygz or gz4=@rygz or gz5=@rygz or @rygz=isnull(gz1,'未输入'))
) bb
on(1=1)

set rowcount 0